package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;

import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import fact.view.DTO.FactDTO;
import fact.view.DTO.NodeFactDTO;
import fact.view.DTO.NodeHierarchyDTO;
import fact.view.DTO.ScenarioDTO;


@ManagedBean(name = "factModelBean")
@RequestScoped
//@ViewScoped
public class FactModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	  

	
	public ArrayList<FactDTO> getFactList(String userId,String month,String year) throws SQLException
	{
		ArrayList<FactDTO> factList=new ArrayList<FactDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select count(*) total from DTL_FACT where userid=? and INSERT_MONTH=? and INSERT_YEAR=?"); 
 
		ps.setString(1, userId);
		ps.setInt(2, Integer.parseInt(month));
		ps.setInt(3, Integer.parseInt(year));
		ResultSet result =  ps.executeQuery();
 
 
		if(result.next())
		{
			if(result.getInt("total")>0)
			{
				ps = con.prepareStatement("Select *  from DTL_FACT,MST_FACT WHERE userid=? and MST_FACT.FIELD_ID=DTL_FACT.FIELDID and INSERT_MONTH=? and INSERT_YEAR=?"); 
				ps.setString(1, userId);
				ps.setInt(2, Integer.parseInt(month));
				ps.setInt(3, Integer.parseInt(year));
				result =  ps.executeQuery();
				
				while(result.next())
				{
					FactDTO f=new FactDTO();
					f.setFactId(result.getInt("FIELD_ID"));
					f.setFactName(result.getString("FIELD_NAME"));
					f.setFactValue(result.getString("FIELDVALUE"));
					
					
					factList.add(f);
				}
			}
			
			else
			{
				
				ps = con.prepareStatement("Select *  from MST_FACT"); 
				result =  ps.executeQuery();
				
				while(result.next())
				{
					FactDTO f=new FactDTO();
					f.setFactId(result.getInt("FIELD_ID"));
					f.setFactName(result.getString("FIELD_NAME"));
					f.setFactValue("");
					
					factList.add(f);
				}
			}
			
		}
		
		
		return factList;
	}
	
	
	public ArrayList<FactDTO> getMstFactList() throws SQLException
	{
		ArrayList<FactDTO> factList=new ArrayList<FactDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_FACT order by FIELD_ID"); 
 
	
		ResultSet result =  ps.executeQuery();
 
 
		
				while(result.next())
				{
					FactDTO f=new FactDTO();
					f.setFactId(result.getInt("FIELD_ID"));
					f.setFactName(result.getString("FIELD_NAME"));
					f.setIsMandatory(result.getInt("ISMANDATORY"));
					f.setValueFrom(result.getInt("VALUE_FROM"));
					f.setValueTo(result.getInt("VALUE_TO"));
					f.setAlignment(result.getString("ALIGNMENT"));
					f.setValidationText(result.getString("VALIDATION_TEXT"));
					f.setIsActive(result.getInt("IS_ACTIVE"));
					
					
					factList.add(f);
				
			
		}
		return factList;
	}
	
	public ArrayList<FactDTO> getBlankSegmentForm(String userId,String segmentId,String month,String year) throws SQLException
	{
		ArrayList<FactDTO> factList=new ArrayList<FactDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		/*
		PreparedStatement ps = con.prepareStatement("SELECT node_name,node_id,parent_id,node_type,(LEVEL * 20) labelPadding " +
													" FROM   DB_FACTENTRY.mst_setting  " +
													" where level>1 " +
													" START WITH node_id= " +segmentId+
													" CONNECT BY PRIOR  node_id=parent_id");  
		*/
		/*
		PreparedStatement ps = con.prepareStatement(" SELECT tmp2.*, s.status FROM (SELECT tmp1.*, fact.field_id, fact.VALUE" +
													" FROM (SELECT     node_name,node_id,parent_id,node_type,(LEVEL * 20) labelPadding,rownum ind " +
													" FROM db_factentry.mst_setting where level>1 " +
													" START WITH node_id= " +segmentId+" CONNECT BY PRIOR node_id = parent_id) tmp1 " +
													" LEFT OUTER JOIN dtl_fact fact ON tmp1.node_id = fact.field_id AND fact.MONTH = 11 AND fact.YEAR = 2012 " +
													" ) tmp2 LEFT OUTER JOIN segment_status s ON tmp2.node_id = s.segment_id AND s.MONTH = 11 AND s.YEAR = 2012 order by ind");
	
		*/
		
		PreparedStatement ps = con.prepareStatement("SELECT tmp1.*, fact.* FROM (SELECT     node_name,node_id,parent_id,node_type,(LEVEL * 20) labelPadding,rownum ind " +
				" FROM db_factentry.mst_setting where level>1  START WITH node_id= " +segmentId+" CONNECT BY PRIOR node_id = parent_id) tmp1  " +
				" LEFT OUTER JOIN (SELECT COMPANY_ID,MARKET_ID,MONTH,YEAR,field_id,SEGMENT_ID, " +
				" MAX(case when seq=1 then value end) scenario1 ," +
				" MAX(case when seq=2 then value end) scenario2," +
				" MAX(case when seq=3 then value end) scenario3," +
				" MAX(case when seq=4 then value end) scenario4," +
				" MAX(case when seq=5 then value end) scenario5," +
				" MAX(case when seq=6 then value end) scenario6," +
				" MAX(case when seq=7 then value end) scenario7," +
				" MAX(case when seq=8 then value end) scenario8," +
				" MAX(case when seq=9 then value end) scenario9," +
				" MAX(case when seq=10 then value end) scenario10 FROM" + 
				" (SELECT DTL_FACT.*, ROW_NUMBER() over(partition by FIELD_ID ORDER BY FIELD_ID) AS seq FROM DTL_FACT)tt" +
				" GROUP BY COMPANY_ID,MARKET_ID,MONTH,YEAR,field_id,SEGMENT_ID) fact ON tmp1.node_id = fact.field_id AND fact.MONTH = 11 AND fact.YEAR = 2012" +
				" order by ind");
		
		
		ResultSet result =  ps.executeQuery();
 
 
		
				while(result.next())
				{
					FactDTO f=new FactDTO();
					f.setFactName(result.getString("node_name"));
					f.setFactId(result.getInt("node_id"));
					//f.setFactValue(result.getString("value"));
					//f.setIsMandatory(result.getInt("parent_id"));
					f.setLabelPadding(result.getInt("labelPadding"));
					f.setNodeType(result.getInt("node_type"));
					//f.setSegmentStatus(result.getString("status"));
					
					f.setScenario1Value(result.getString("scenario1"));
					f.setScenario2Value(result.getString("scenario2"));
					f.setScenario3Value(result.getString("scenario3"));
					f.setScenario4Value(result.getString("scenario4"));
					f.setScenario5Value(result.getString("scenario5"));
					f.setScenario6Value(result.getString("scenario6"));
					f.setScenario7Value(result.getString("scenario7"));
					f.setScenario8Value(result.getString("scenario8"));
					f.setScenario9Value(result.getString("scenario9"));
					f.setScenario10Value(result.getString("scenario10"));
					factList.add(f);
				
			
		}
		return factList;
	}
	
	public ArrayList<ScenarioDTO> getAllScenario() throws SQLException
	{
		ArrayList<ScenarioDTO> scenarioList=new ArrayList<ScenarioDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SCENARIO order by POSITION_NO");  
 
	
		ResultSet result =  ps.executeQuery();
 
 
		
				while(result.next())
				{
					ScenarioDTO sdto=new ScenarioDTO();
					sdto.setScenarioId(result.getInt("SCENARIO_ID"));
					sdto.setScenarioName(result.getString("SCENARIO_NAME"));
					scenarioList.add(sdto);
				
			
		}
		return scenarioList;
	}
	
	
	
	public boolean saveFacts(List<FactDTO> factList,String userId,String month,String year) throws SQLException, ClassNotFoundException 
	{
		ArrayList<FactDTO> facts=(ArrayList<FactDTO>)factList;
		String[] fId=new String[facts.size()];
		String[] fValue=new String[facts.size()];
		
		for(int i=0;i<facts.size();i++)
		{
			FactDTO factDto=facts.get(i);
			fId[i]=String.valueOf(factDto.getFactId());
			fValue[i]=factDto.getFactValue();
			
			/*
			 System.out.println("Fact Name====>>"+factDto.getFactName());
			 System.out.println("Fact Value====>>"+factDto.getFactValue());
			 System.out.println("-----------------------------------------");
			 */
			
		}
		
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
		
		OracleCallableStatement stmt = null;
		
//		Class.forName("oracle.jdbc.driver.OracleDriver");
//		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//		
//		Connection conn =
//			DriverManager.getConnection(
//			"jdbc:oracle:thin:@127.0.0.1:1521:XE",       // URL
//			"db_factentry",       // username
//			"Ifti1Gio2"        // password
//			);
		//Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "db_factentry", "Ifti1Gio2");
		
		try {
		ArrayDescriptor arrayDescriptor = new ArrayDescriptor("VARCHARARRAY", con);

		ARRAY inputFactId = new ARRAY(arrayDescriptor, con, fId);
		ARRAY inputFactValue = new ARRAY(arrayDescriptor, con, fValue);
		stmt = (OracleCallableStatement) con .prepareCall("{ call saveFacts(?,?,?,?,?) }");

		stmt.setString(1, userId);
		stmt.setInt(2, Integer.valueOf(month));
		stmt.setInt(3, Integer.valueOf(year));
		stmt.setArray(4, inputFactId);
		stmt.setArray(5, inputFactValue);

		stmt.executeUpdate();


		}
		catch (Exception e){e.printStackTrace();return false;}
		
		
		System.out.println("Here I'm ");
		return true;
    }


	public static void main(String args[])
	{
		System.out.println("aaa");
	}

	public boolean updateMstFact(FactDTO fdto) throws SQLException
	{
		ArrayList<FactDTO> factList=new ArrayList<FactDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update Mst_Fact set field_name=?,ismandatory=?,value_from=?,value_to=?,alignment=?,validation_text=?,is_active=?where field_id=?"); 
 
		ps.setString(1, fdto.getFactName());
		ps.setInt(2, fdto.getIsMandatory());
		ps.setInt(3, fdto.getValueFrom());
		ps.setInt(4, fdto.getValueTo());
		ps.setString(5, fdto.getAlignment());
		ps.setString(6, fdto.getValidationText());
		ps.setInt(7, fdto.getIsActive());
		ps.setInt(8,fdto.getFactId());
		ps.executeUpdate();
 
		
		
		return true;
	}
	
	
	public ArrayList<NodeHierarchyDTO> getNodeHierarchy(int segmentId,int makeId,int marketId,int month,int year) throws SQLException
	{
		ArrayList<NodeHierarchyDTO> nodeHierarchyList=new ArrayList<NodeHierarchyDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
		ResultSet result=null;
		PreparedStatement ps=null;
		HashMap<String, ArrayList<NodeFactDTO>> nodeFactsHashMap=getNodeFacts1(segmentId, makeId, marketId, month, year);
		try
		{
		ps = con.prepareStatement("select * from node_hierarchy Where Segment_id=? Order by Position"); 
		
		ps.setInt(1, segmentId);
		result =  ps.executeQuery();
		
		NodeHierarchyDTO nhDTO=null;
		while(result.next())
		{
			nhDTO=new NodeHierarchyDTO();
			
			nhDTO.setNodeId(result.getInt("NODE_ID"));
			nhDTO.setDataType(result.getString("DATA_TYPE"));
			nhDTO.setDescription(result.getString("DESCRIPTION"));
			nhDTO.setLabel(result.getString("LABEL"));
			nhDTO.setPosition(result.getInt("POSITION"));
			nhDTO.setParentNode(result.getInt("PARENT_NODE"));
			nhDTO.setNodeType(result.getInt("NODE_TYPE_ID"));
			
			System.out.println(result.getInt("NODE_ID"));
			
			System.out.println("---Before--");
			//nhDTO.setNodeFactList(getNodeFacts(segmentId, result.getInt("NODE_ID"), makeId, marketId, month, year));
			nhDTO.setNodeFactList(getNodeFacts2(nodeFactsHashMap,segmentId, result.getInt("NODE_ID"), makeId, marketId, month, year));
			System.out.println("---After--");
			
			nodeHierarchyList.add(nhDTO);
		}
	}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
	      {
	        if (result != null)
	        {
	        	result.close();
	        	result=null;
	        }

	        if (ps != null)
	        {
	          ps.close();
	          ps=null;
	        }

	        if (con != null)
	        {
	          con.close();
	          con=null;
	        }
	        
	      }
		
		return nodeHierarchyList;
	}
	
	
	public ArrayList<NodeFactDTO> getNodeFacts(int segmentId,int nodeId, int makeId,int marketId,int month,int year) throws SQLException
	{
		ArrayList<NodeFactDTO> factList=new ArrayList<NodeFactDTO>();
		Connection con=null;
		ResultSet result=null;
		PreparedStatement ps=null;
		try
		{
		if(ds==null)
			throw new SQLException("Can't get data source");
		 con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
		
		
		
		ps = con.prepareStatement("Select * from Node_Facts nf, MST_SCENARIO ms,NODE_SCENARIO_ATTRIBUTES msa Where" +
				" nf.Node_Id=? and Entry_Set_Id=(Select Entry_Set_Id from ENTRY_SETS Where Month=? and Year=? and  " +
				" Make_Id=? and Market_Id=? and Segment_id=?) " +
				" and nf.SCENARIO_ID=ms.SCENARIO_ID " +
				" and ms.SCENARIO_ID=msa.SCENARIO_ID " +
				" and nf.NODE_ID=msa.NODE_ID " +				
				" and msa.VISIBILITY_FLAG=1 order by ms.SCENARIO_ID"); 
		
		ps.setInt(1, nodeId);
		ps.setInt(2, month);
		ps.setInt(3, year);
		ps.setInt(4, makeId);
		ps.setInt(5, marketId);
		ps.setInt(6, segmentId);		
		result =  ps.executeQuery();
		
		NodeFactDTO nfDTO=null;
		while(result.next())
		{
			nfDTO=new NodeFactDTO();
			nfDTO.setEntrySetId(result.getInt("ENTRY_SET_ID"));
			nfDTO.setMakeId(result.getInt("MAKE_ID"));
			nfDTO.setNodeId(result.getInt("NODE_ID"));
			nfDTO.setScenarioId(result.getInt("SCENARIO_ID"));
			nfDTO.setScenarioName(result.getString("SCENARIO_NAME"));
			nfDTO.setPosition(result.getInt("POSITION_NO"));
			nfDTO.setVisibility(result.getBoolean("VISIBILITY_FLAG"));
			nfDTO.setMinValue(result.getInt("MIN_VALUE"));
			nfDTO.setMaxValue(result.getInt("MAX_VALUE"));	
			nfDTO.setValue(result.getInt("VALUE"));	
			factList.add(nfDTO);
		}
		}
		
		catch(Exception ex)
		{
			System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
			ex.printStackTrace();
		}
		
		finally
	      {
	        if (result != null)
	        {
	        	result.close();
	        	result=null;
	        }

	        if (ps != null)
	        {
	          ps.close();
	          ps=null;
	        }

	        if (con != null)
	        {
	          con.close();
	          con=null;
	        }
	     
	      }
	        
		
		return factList;
	}
	
	
	public ArrayList<NodeFactDTO> getNodeFacts2(HashMap<String, ArrayList<NodeFactDTO>> nodeFactsHashMap,int segmentId,int nodeId, int makeId,int marketId,int month,int year) throws SQLException
	{
		ArrayList<NodeFactDTO> factList=new ArrayList<NodeFactDTO>();
		String key=segmentId+""+nodeId+""+makeId+""+marketId+""+month+""+year;
		factList=nodeFactsHashMap.get(key);
		return factList;
	}
	
	public HashMap<String, ArrayList<NodeFactDTO>> getNodeFacts1(int segmentId, int makeId,int marketId,int month,int year) throws SQLException
	{
		ArrayList<NodeFactDTO> factList=new ArrayList<NodeFactDTO>();
		HashMap<String, ArrayList<NodeFactDTO>> hashMap=new HashMap<String, ArrayList<NodeFactDTO>>();
		Connection con=null;
		ResultSet result=null;
		PreparedStatement ps=null;
		try
		{
		if(ds==null)
			throw new SQLException("Can't get data source");
		 con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
		
		
		
		ps = con.prepareStatement("Select * from Node_Facts nf, MST_SCENARIO ms,NODE_SCENARIO_ATTRIBUTES msa Where" +
				" Entry_Set_Id=(Select Entry_Set_Id from ENTRY_SETS Where Month=? and Year=? and  " +
				" Make_Id=? and Market_Id=? and Segment_id=?) " +
				" and nf.SCENARIO_ID=ms.SCENARIO_ID " +
				" and ms.SCENARIO_ID=msa.SCENARIO_ID " +
				" and nf.NODE_ID=msa.NODE_ID " +
				" And msa.MAKE_ID=? "+
				" and msa.VISIBILITY_FLAG=1 order by nf.Node_Id,ms.SCENARIO_ID"); 
		
		ps.setInt(1, month);
		ps.setInt(2, year);
		ps.setInt(3, makeId);
		ps.setInt(4, marketId);
		ps.setInt(5, segmentId);
		ps.setInt(6, makeId);
		result =  ps.executeQuery();
		String key="";
		NodeFactDTO nfDTO=null;
		int preNode=0;
		boolean flag=false;
		while(result.next())
		{

			if(preNode!=result.getInt("Node_Id") && preNode!=0)
			{
				key=segmentId+""+preNode+""+makeId+""+marketId+""+month+""+year;
				hashMap.put(key, factList);
				factList=new ArrayList<NodeFactDTO>();
				flag=false;				
			}
			
			nfDTO=new NodeFactDTO();
			nfDTO.setEntrySetId(result.getInt("ENTRY_SET_ID"));
			nfDTO.setMakeId(result.getInt("MAKE_ID"));
			nfDTO.setNodeId(result.getInt("NODE_ID"));
			nfDTO.setScenarioId(result.getInt("SCENARIO_ID"));
			nfDTO.setScenarioName(result.getString("SCENARIO_NAME"));
			nfDTO.setPosition(result.getInt("POSITION_NO"));
			nfDTO.setVisibility(result.getBoolean("VISIBILITY_FLAG"));
			nfDTO.setMinValue(result.getInt("MIN_VALUE"));
			nfDTO.setMaxValue(result.getInt("MAX_VALUE"));	
			nfDTO.setValue(result.getInt("VALUE"));	
			factList.add(nfDTO);
			flag=true;
			
			
			
		
			
			
			
			preNode=result.getInt("Node_Id");
		}
		
		if(flag==true)
		{
			key=segmentId+""+preNode+""+makeId+""+marketId+""+month+""+year;
			hashMap.put(key, factList);
		}
		}
		
		catch(Exception ex)
		{
			System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
			ex.printStackTrace();
		}
		
		finally
	      {
	        if (result != null)
	        {
	        	result.close();
	        	result=null;
	        }

	        if (ps != null)
	        {
	          ps.close();
	          ps=null;
	        }

	        if (con != null)
	        {
	          con.close();
	          con=null;
	        }
	     
	      }
	        
		
		return hashMap;
	}
	
	
	
	/*
	 * select * from node_hierarchy Where Segment_id=1
Order by Position

Select * from Node_Facts nf, MST_SCENARIO ms,NODE_SCENARIO_ATTRIBUTES msa Where    
nf.Node_Id=10 and Entry_Set_Id=(Select Entry_Set_Id from ENTRY_SETS Where Month=11 and Year=2012 and Make_Id=1 and Market_Id=1 and Segment_id=1)
and nf.SCENARIO_ID=ms.SCENARIO_ID
and ms.SCENARIO_ID=msa.SCENARIO_ID
and nf.NODE_ID=msa.NODE_ID
order by ms.SCENARIO_ID
	 */
	

}
